package net.dao;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.ClassUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;

import net.service.SpringContextAgent;

public abstract class AbstractSpringDAO extends JdbcDaoSupport {

	/**
	 * Logger for this class
	 */
	private static final Log logger = LogFactory.getLog(AbstractSpringDAO.class);

	private String schema = null;

	private String dbType = null;

	private SQLBuilder sqlBuilder;

	private String DATA_BASE_PRODUCT_NAME;

	private static final List<String> EXCLUDE_DEBU_LOG_CLASS_LIST = new ArrayList<String>();

	private static final String[] DATE_PATTERNS = { "yyyy-MM-dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss", "yyyyMMdd HHmmss" };

	public AbstractSpringDAO() {
		super();
	}

	public static final void addExcludeDebugLogClass(String className) {
		if (EXCLUDE_DEBU_LOG_CLASS_LIST.contains(className) == false) {
			EXCLUDE_DEBU_LOG_CLASS_LIST.add(className);
		}
	}

	public static final void removeExcludeDebugLogClass(String className) {
		if (EXCLUDE_DEBU_LOG_CLASS_LIST.contains(className) == true) {
			EXCLUDE_DEBU_LOG_CLASS_LIST.remove(className);
		}
	}

	@Override
	protected void initDao() throws Exception {
		super.initDao();
		getJdbcTemplate()
				.setNativeJdbcExtractor((NativeJdbcExtractor) SpringContextAgent.getBean("nativeJdbcExtractor"));
	}

	protected abstract String getEntityName();

	protected abstract String[] getEntityFields();

	protected abstract String[] getEntityProperties();

	protected abstract String[] getEntityPKFields();

	protected abstract String[] getEntityAutoGenPKFields();

	protected abstract String[] getEntityUniqueFields();

	protected abstract String getRelatedJoinSQL();

	protected abstract String getBaiscQueryFilter();

	public SQLBuilder getSqlBuilder() {
		if (sqlBuilder == null) {
			sqlBuilder = new SQLBuilder(getDatabaseType());
			sqlBuilder.setBaiscQueryFilter(getBaiscQueryFilter());
			sqlBuilder.setEntityAutoGenPKFields(getEntityAutoGenPKFields());
			sqlBuilder.setEntityFields(getEntityFields());
			sqlBuilder.setEntityName(getEntityName());
			sqlBuilder.setEntityPKFields(getEntityPKFields());
			sqlBuilder.setEntityProperties(getEntityProperties());
			sqlBuilder.setEntityUniqueFields(getEntityUniqueFields());
			sqlBuilder.setRelatedJoinSQL(getRelatedJoinSQL());
		}
		
		schema = DAOThreadVariable.getScheam();
		dbType = DAOThreadVariable.getDBType();
		
		sqlBuilder.setDbType(dbType);
		sqlBuilder.setSchema(schema);
		
		return sqlBuilder;
	}

	public String getDatabaseType() {
		if (DATA_BASE_PRODUCT_NAME == null) {
			try {
				Connection connection = getJdbcTemplate().getDataSource().getConnection();

				DATA_BASE_PRODUCT_NAME = StringUtils.lowerCase(connection.getMetaData().getDatabaseProductName());

				connection.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}

		}
		return DATA_BASE_PRODUCT_NAME;

	}

	static protected void setBeanValue(Object object, String property, Object value) throws Exception {

		if (value instanceof Clob) {

			Clob notes = (Clob) value;

			long len = notes.length();

			String str = notes.getSubString(1, (int) len);

			BeanUtils.setProperty(object, property, str);
		} else if (value instanceof String && StringUtils.isNotBlank((String) value)
				&& ClassUtils.isAssignable(PropertyUtils.getPropertyType(object, property), Date.class)) {

			if (StringUtils.isNumeric((String) value)) {
				Date date = new Date(Long.parseLong((String) value));

				BeanUtils.setProperty(object, property, date);
			} else {

				Date date = DateUtils.parseDate((String) value, DATE_PATTERNS);

				BeanUtils.setProperty(object, property, date);
			}

		} else {
			BeanUtils.setProperty(object, property, value);
		}
	}

	protected void debugSQL(String sql, List<Object> parameters) {
		if (logger.isDebugEnabled()) {
			String value;
			for (int j = 0; j < parameters.size(); j++) {
				value = String.valueOf(parameters.get(j));
				if (parameters.get(j) instanceof String) {
					value = "'" + value + "'";
				} else if (parameters.get(j) instanceof Date) {
					value = DateFormatUtils.format((Date) parameters.get(j), "yyyy-MM-dd HH:mm:ss");
				}

				sql = StringUtils.replaceOnce(sql, "?", value);

			}
			logger.debug(sql);
		}
	}

	protected void debugSQL(String sql) {
		debugSQL(sql, new ArrayList<Object>());
	}

	public void doDeleteByIds(Object... ids) throws Exception {

		StringBuffer sql = new StringBuffer();

		sql.append(getSqlBuilder().buildDeleteByIdSQL());

		List<Object> objects = new ArrayList<Object>();

		for (Object id : ids) {
			objects.add(id);
		}

		if (ArrayUtils.isNotEmpty(ids)) {
			debugSQL(sql.toString(), objects);
		} else {
			debugSQL(sql.toString());
		}

		this.getJdbcTemplate().update(sql.toString(), ids);
	}

	public void doDeleteById(Object[] ids) throws Exception {

		StringBuffer sql = new StringBuffer();

		sql.append(getSqlBuilder().buildDeleteByIdSQL());

		if (ArrayUtils.isNotEmpty(ids)) {
			debugSQL(sql.toString(), Arrays.asList(ids));
		} else {
			debugSQL(sql.toString());
		}

		this.getJdbcTemplate().update(sql.toString(), ids);
	}

	public void doBatchInsert(final Object[] aObjects) throws Exception {
		if (ArrayUtils.isEmpty(aObjects)) {
			return;
		}

		if (aObjects.length == 0) {
			doInsert(aObjects[0]);
		} else {

			final List<Object> insertValueProperties = new ArrayList<Object>();

			StringBuffer sql = new StringBuffer(getSqlBuilder().buildBatchInsertSQL(aObjects, insertValueProperties));

			debugSQL(sql.toString());

			for (int i = 0; i < aObjects.length; i++) {
				beforeInsert(aObjects[i], getEntityAutoGenPKFields());
			}

			this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

				public int getBatchSize() {
					return aObjects.length;
				}

				public void setValues(PreparedStatement preparedStatement, int parameterIndex) throws SQLException {
					try {
						for (int i = 0; i < insertValueProperties.size(); i++) {
							String property = (String) insertValueProperties.get(i);

							Object value = PropertyUtils.getProperty(aObjects[parameterIndex], property);

							if (value instanceof Date) {
								preparedStatement.setTimestamp(i + 1, new java.sql.Timestamp(((Date) value).getTime()));

							} else {
								preparedStatement.setObject(i + 1, value);
							}

						}
					} catch (Exception e) {
						logger.error("batch insert error", e);
						throw new RuntimeException(e);
					}
				}

			});

		}
	}

	public void doBatchUpdate(final Object[] aObjects) throws Exception {
		if (ArrayUtils.isEmpty(aObjects)) {
			return;
		}

		if (aObjects.length == 0) {
			doUpdate(aObjects[0]);
		} else {

			final List<Object> pkProperties = new ArrayList<Object>();

			final List<Object> updateProperties = new ArrayList<Object>();

			StringBuffer sql = new StringBuffer(
					getSqlBuilder().buildBatchUpdateSQL(aObjects, pkProperties, updateProperties));

			debugSQL(sql.toString());

			this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

				public int getBatchSize() {
					return aObjects.length;
				}

				public void setValues(PreparedStatement preparedStatement, int parameterIndex) throws SQLException {
					try {
						for (int i = 0; i < updateProperties.size(); i++) {
							String property = (String) updateProperties.get(i);

							Object value = PropertyUtils.getProperty(aObjects[parameterIndex], property);

							if (value instanceof Date) {
								preparedStatement.setTimestamp(i + 1, new java.sql.Timestamp(((Date) value).getTime()));

							} else {
								preparedStatement.setObject(i + 1, value);
							}

						}

						for (int i = 0; i < pkProperties.size(); i++) {
							String property = (String) pkProperties.get(i);

							preparedStatement.setObject(i + updateProperties.size() + 1,
									PropertyUtils.getProperty(aObjects[parameterIndex], property));

						}
					} catch (Exception e) {
						logger.error("batch update error", e);
						throw new RuntimeException(e);
					}
				}

			});

		}
	}

	public void doInsert(Object aObject) throws Exception {

		final String[] autoGenPkFields = getEntityAutoGenPKFields();

		beforeInsert(aObject, autoGenPkFields);

		final List<Object> parameters = new ArrayList<Object>();

		final StringBuffer sql = new StringBuffer(getSqlBuilder().buildInsertSQL(aObject, parameters));

		debugSQL(sql.toString(), parameters);

		KeyHolder keyHolder = new GeneratedKeyHolder();

		this.getJdbcTemplate().update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql.toString(), autoGenPkFields);
				int index = 1;
				for (Iterator<Object> iterator = parameters.iterator(); iterator.hasNext();) {
					ps.setObject(index++, iterator.next());
				}
				return ps;
			}
		}, keyHolder);

		for (int i = 0; i < autoGenPkFields.length; i++) {

			Object id = keyHolder.getKeyList().get(i).get("GENERATED_KEY");

			String propertyName = convertEntityFieldToProperty(autoGenPkFields[i]);

			Class propertyType = PropertyUtils.getPropertyType(aObject, propertyName);

			if (Long.class.equals(propertyType) || long.class.equals(propertyType)) {
				PropertyUtils.setProperty(aObject, propertyName, new Long(id.toString()));
			} else if (Integer.class.equals(propertyType) || int.class.equals(propertyType)) {
				PropertyUtils.setProperty(aObject, propertyName, new Integer(id.toString()));
			} else {
				PropertyUtils.setProperty(aObject, propertyName,
						ConvertUtils.convert(new Long(id.toString()), propertyType));
			}

		}

		// this.getJdbcTemplate().update(sql.toString(), parameters.toArray());

		// afterInsert(aObject, autoGenPkFields);
	}

	public void doUpdate(Object aObject) throws Exception {
		List<Object> parameters = new ArrayList<Object>();

		List<Object> pkParameters = new ArrayList<Object>();

		StringBuffer sql = new StringBuffer(getSqlBuilder().buildUpdateSQL(aObject, pkParameters, parameters));

		debugSQL(sql.toString(), parameters);

		this.getJdbcTemplate().update(sql.toString(), parameters.toArray());
	}

	public void doUpdateByQuery(BaseQueryBean aQueryBean) throws Exception {

		List<Object> parameters = new ArrayList<Object>();

		String sql = getSqlBuilder().buildUpdateSQL(aQueryBean, parameters);

		debugSQL(sql, parameters);

		this.getJdbcTemplate().update(sql, parameters.toArray());
	}

	public void doUpdateByQuery(Object aObject, BaseQueryBean aQueryBean) throws Exception {

		doUpdateByQuery(getEntityName(), getEntityFields(), getEntityProperties(), null, aObject, aQueryBean);
	}

	public void doUpdateByQuery(String entityName, String[] entityFields, String[] entityProperties, String joinSQL,
			Object aObject, BaseQueryBean aQueryBean) throws Exception {

		String[] fields = entityFields;

		List<Object> parameters = new ArrayList<Object>();

		StringBuffer sql = new StringBuffer(getSqlBuilder().buildUpdateByQuerySQL(entityName, fields, entityProperties,
				joinSQL, aObject, aQueryBean, parameters));

		debugSQL(sql.toString(), parameters);

		this.getJdbcTemplate().update(sql.toString(), parameters.toArray());

	}

	public void doDeleteByQuery(BaseQueryBean aQueryBean) throws Exception {
		if (getSqlBuilder().getDatabaseType().equals(SQLBuilder.MYSQL)) {
			doDeleteByQuery(getEntityName(), getRelatedJoinSQL(), aQueryBean);
		} else {
			doDeleteByQuery(getEntityName(), null, aQueryBean);
		}

	}

	public void doDeleteByQuery(String entityName, String joinSQL, BaseQueryBean aQueryBean) throws Exception {

		List<Object> parameters = new ArrayList<Object>();

		StringBuffer sql = new StringBuffer(
				getSqlBuilder().buildDeleteByQuerySQL(entityName, joinSQL, aQueryBean, parameters));

		debugSQL(sql.toString(), parameters);

		this.getJdbcTemplate().update(sql.toString(), parameters.toArray());

	}

	public Object doFindById(Object[] ids, RowMapper rowMapper) throws Exception {

		StringBuffer sql = new StringBuffer(getSqlBuilder().buildFindByIdSQL(ids));

		if (ArrayUtils.isNotEmpty(ids)) {
			debugSQL(sql.toString(), Arrays.asList(ids));
		} else {
			debugSQL(sql.toString());
		}

		return this.getJdbcTemplate().queryForObject(sql.toString(), ids, rowMapper);
	}

	public Map<String, Object>[] doQuerySelective(String entityName, BaseQueryBean aQueryBean, String aJionSQL)
			throws Exception {

		List<Object> queryParameters = new ArrayList<Object>();

		String pagingSQL = getSqlBuilder().buildQuerySelectiveSQL(entityName, aQueryBean, aJionSQL, queryParameters);

		debugSQL(pagingSQL, queryParameters);

		List resultList = this.getJdbcTemplate().queryForList(pagingSQL, queryParameters.toArray());

		return (Map[]) resultList.toArray(new Map[resultList.size()]);
	}

	public Map<String, Object>[] doQuerySelective(BaseQueryBean aQueryBean) throws Exception {

		return doQuerySelective(getEntityName(), aQueryBean, null);
	}

	public List<Object> doQuery(BaseQueryBean aQueryBean, String aJionSQL, RowMapper rowMapper) throws Exception {
		return doQuery(getEntityName(), getEntityFields(), getEntityProperties(), aQueryBean, aJionSQL, rowMapper);
	}

	public List<Object> doQuery(String entityName, String[] entityFields, String[] entityProperties,
			BaseQueryBean aQueryBean, String aJionSQL, RowMapper rowMapper) throws Exception {

		SQLBean querySQLBean = getSqlBuilder().getQuerySQLBean(entityName, entityFields, entityProperties, aQueryBean,
				aJionSQL);

		if (ArrayUtils.isNotEmpty(querySQLBean.getParameters())) {
			debugSQL(querySQLBean.getSql(), Arrays.asList(querySQLBean.getParameters()));
		} else {
			debugSQL(querySQLBean.getSql());
		}

		List<Object> list = this.getJdbcTemplate().query(querySQLBean.getSql(), querySQLBean.getParameters(),
				rowMapper);

		return list;
	}

	public List<Object> doQuery(BaseQueryBean aQueryBean, RowMapper rowMapper) throws Exception {

		return doQuery(aQueryBean, null, rowMapper);
	}

	public int doQueryCount(BaseQueryBean aQueryBean) throws Exception {

		List<Object> queryParameters = new ArrayList<Object>();

		String pagingSQL = getSqlBuilder().buildQueryCountSQL(aQueryBean, queryParameters);

		debugSQL(pagingSQL, queryParameters);

		return this.getJdbcTemplate().queryForObject(pagingSQL, queryParameters.toArray(), Integer.class);

	}

	public boolean isUnique(Object[] aValues, Object[] aPks) throws Exception {

		if (ArrayUtils.isEmpty(getEntityUniqueFields()) || aValues == null || aValues.length == 0) {
			return true;
		}

		List<Object> parameters = new ArrayList<Object>();

		StringBuffer sql = new StringBuffer(getSqlBuilder().buildIsUniqueSQL(aValues, aPks, parameters));

		debugSQL(sql.toString(), parameters);

		int count = this.getJdbcTemplate().queryForObject(sql.toString(), parameters.toArray(), Integer.class);

		if (count > 0) {
			return false;
		} else {
			return true;
		}

	}

	protected void afterInsert(Object aObject, String[] autoGenPkFields) throws Exception {
		if (SQLBuilder.MYSQL.equalsIgnoreCase(getDatabaseType())
				|| SQLBuilder.MSSQLSERVER.equalsIgnoreCase(getDatabaseType())
				|| SQLBuilder.SQLITE.equalsIgnoreCase(getDatabaseType())) {
			long[] ids = new long[autoGenPkFields.length];
			String[] sqls = getSqlBuilder().getAutoGenPkFields(autoGenPkFields);
			for (int i = 0; i < ids.length; i++) {

				long id = this.getJdbcTemplate().queryForObject(sqls[i], Long.class);

				String propertyName = convertEntityFieldToProperty(autoGenPkFields[i]);

				Class propertyType = PropertyUtils.getPropertyType(aObject, propertyName);

				if (Long.class.equals(propertyType) || long.class.equals(propertyType)) {
					PropertyUtils.setProperty(aObject, propertyName, new Long(id));
				} else if (Integer.class.equals(propertyType) || int.class.equals(propertyType)) {
					PropertyUtils.setProperty(aObject, propertyName, new Integer((int) id));
				} else {
					PropertyUtils.setProperty(aObject, propertyName, ConvertUtils.convert(new Long(id), propertyType));
				}

			}

		}

	}

	protected void beforeInsert(Object aObject, String[] autoGenPkFields) throws Exception {
		if (SQLBuilder.ORACLE.equalsIgnoreCase(getDatabaseType())) {

			long[] ids = new long[autoGenPkFields.length];

			String[] sqls = getSqlBuilder().getAutoGenPkFields(autoGenPkFields);

			for (int i = 0; i < ids.length; i++) {
				long id = this.getJdbcTemplate().queryForObject(sqls[i], Long.class);

				String propertyName = convertEntityFieldToProperty(autoGenPkFields[i]);

				PropertyUtils.setProperty(aObject, propertyName, new Long(id));
			}
		} else if (SQLBuilder.MSSQLSERVER.equalsIgnoreCase(getDatabaseType())) {
			// this.getJdbcTemplate().execute(
			// "SET IDENTITY_INSERT " + getEntityName() + " ON");
		}
	}

	protected String convertEntityFieldToProperty(String aFieldName) {
		String[] fields = getEntityFields();

		String[] properties = getEntityProperties();

		String propertyName = null;

		for (int i = 0; i < fields.length; i++) {
			String field = fields[i];

			if (field.equals(aFieldName)) {
				propertyName = properties[i];
				break;
			}
		}

		return propertyName;
	}
}
